import Excel from 'exceljs';
import fs from 'fs';
import mysql from 'mysql2/promise';


async function createConnection() {
    return mysql.createConnection({
        host: '10.17.65.105',       // 数据库主机名，默认为 localhost
        port: 8087,              // 默认端口是 3306
        user: '-',            // 用户名
        password: '-',// 密码
        database: 'spd'      // 要使用的数据库名称
    });
}

function queryCallback(results, newPrice, arr) {
    if (results && results.length > 0) {
        for (let i = 0; i < results.length; i++) {
            let rec = results[i];
            let str1 = `-- ${rec.id} ${rec.prov_id} ${rec.sub_prov_id} ${rec.hos_goods_id} ${rec.purchase_price}`;
            let str2 = `update supply_prov_hos_goods set purchase_price = ${newPrice},version=version+1,last_modified=now() where id = '${rec.id}';`;
            arr.push(str1);
            arr.push(str2);
            console.log('queryCallback >>> ' + str2);
        }
    }
    // str = str + `-- ${rs[0].id} ${rs[0].prov_id} ${rs[0].sub_prov_id} ${rs[0].hos_goods_id} ${rs[0].purchase_price} \n`;
}


function readExcel(fileName) {

    let workbook = new Excel.Workbook();
    workbook.xlsx.readFile(fileName).then(async function () {
        let worksheet = workbook.getWorksheet(1); //获取第一个worksheet
        try {
            let arr = [];
            const conn = await createConnection();
            let rows = worksheet.getRows(3, worksheet.rowCount);
            for (let rowNumber = 0; rowNumber < rows.length; rowNumber++) {
                let row = rows[rowNumber];
                let cell1 = row.getCell(1);
                let cell6 = row.getCell(6);
                const sql = `
                        select id, version, prov_id, sub_prov_id, hos_goods_id, purchase_price, sale_price
                        from supply_prov_hos_goods where hos_id = 'h233e' and hos_goods_id = 'h233e${cell1.value}'
                        and mcms_goods_state = 30 and active_flag = 1
                    `;
                const [ results ] = await conn.execute(sql);
                queryCallback(results, cell6.value, arr);
                console.log(`第${rowNumber}行，产品编码${cell1.value}, 价格：${cell6.value}`);
            }
            await conn.end();
            const str = arr.join('\n');
            fs.writeFile('dist/output_N202502281013594.sql', str, (err) => {
                if (err) throw err;
                console.log('文件已被保存');
            });
        } catch (e) {
            console.error(e);
        } finally {
            // process.exit();
        }
    });
}

// 调用函数
readExcel('dist/N202502281013594.xlsx');